﻿using BCCommon;
using BCEntity.TMS.Order.OrderAssignRecord;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.Order.OrderAssignRecord
{

    public class OrderAssignRecordData : IOrderAssignRecordData
    {
        private const string QUERY = @"SELECT OrderAssignRecordId, OrderId, AdminId, DepartmentId, CompanyId, IsAssign, Reason, CreateTime,
                                        UpdateTime,VehicleNo,DriverId,DriverName,IsOrderReceive,ReceiveTime,AssignEditerId,AssignEditer,AssignTime,AssignTimeOut 
                                    FROM  `OrderAssignRecord` ";
        private const string QUERY_JOIN = @"SELECT A.OrderAssignRecordId,A.OrderId,A.AdminId,B.Avatar,A.DepartmentId,A.CompanyId,A.IsAssign,A.Reason,A.CreateTime,A.UpdateTime,
                                            A.VehicleNo,A.DriverId,A.DriverName,A.IsOrderReceive,A.ReceiveTime,A.AssignEditerId,A.AssignEditer,A.AssignTime,A.AssignTimeOut,
                                            B.FullName AS AdminName,B.CompanyName,C.OrderNo,D.DepartmentName 
                                            FROM Orderassignrecord A 
                                            LEFT JOIN `Admin` B ON A.AdminId = B.AdminId 
                                            LEFT JOIN `Orders` C ON C.OrderId = A.OrderId 
                                            LEFT JOIN Department D ON D.DepartmentId = A.DepartmentId ";

        private readonly IDatabaseContext database;

        public OrderAssignRecordData(IDatabaseContext database)
        {
            this.database = database;
        }

        #region InsertAsync
        /// <summary>
        /// 添加新订单指派纪录
        /// </summary>
        /// <param name="orderAssignRecordEntity">订单指派纪录</param>
        /// <returns></returns>
        public async Task<OrderAssignRecordEntity> InsertAsync(OrderAssignRecordEntity orderAssignRecordEntity)
        {
            string sql = SqlCommands.GetInsertSql("OrderAssignRecord", new[]{
             "OrderId",
             "AdminId",
             "DepartmentId",
             "CompanyId",
             "IsAssign",
             "Reason","VehicleNo","DriverId","DriverName","AssignEditerId","AssignEditer","AssignTime","AssignTimeOut"
            });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderAssignRecordEntity.OrderId, DbType.Int64);
            parameters.Add("?AdminId", orderAssignRecordEntity.AdminId, DbType.Int64);
            parameters.Add("?DepartmentId", orderAssignRecordEntity.DepartmentId, DbType.Int32);
            parameters.Add("?CompanyId", orderAssignRecordEntity.CompanyId, DbType.Int32);
            parameters.Add("?IsAssign", orderAssignRecordEntity.IsAssign, DbType.Boolean);
            parameters.Add("?Reason", orderAssignRecordEntity.Reason, DbType.AnsiString);
            parameters.Add("?VehicleNo", orderAssignRecordEntity.VehicleNo, DbType.AnsiString);
            parameters.Add("?DriverId", orderAssignRecordEntity.DriverId ?? null, DbType.Int64);
            parameters.Add("?DriverName", orderAssignRecordEntity.DriverName, DbType.AnsiString);
            parameters.Add("?AssignEditerId", orderAssignRecordEntity.AssignEditerId ?? null, DbType.Int64);
            parameters.Add("?AssignEditer", orderAssignRecordEntity.AssignEditer, DbType.AnsiString);
            parameters.Add("?AssignTime", orderAssignRecordEntity.AssignTime ?? null, DbType.DateTime);
            parameters.Add("?AssignTimeOut", orderAssignRecordEntity.AssignTimeOut ?? null, DbType.DateTime);
            sql = string.Format("{0} {1} WHERE OrderAssignRecordId=LAST_INSERT_ID();", sql, QUERY_JOIN);

            return await database.Master.QueryFirstOrDefaultAsync<OrderAssignRecordEntity>(sql, parameters, database.Transaction);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改订单指派记录
        /// </summary>
        /// <param name="orderAssignRecordEntity">订单指派纪录</param>
        /// <returns></returns>
        public async Task<OrderAssignRecordEntity> UpdateAsync(OrderAssignRecordEntity orderAssignRecordEntity)
        {
            string sql = SqlCommands.GetUpdateSql("OrderAssignRecord", new[]{
             "OrderId",
             "AdminId",
             "DepartmentId",
             "CompanyId",
             "IsAssign",
             "Reason",
             "UpdateTime",
            }, new[] { "OrderAssignRecordId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderAssignRecordId", orderAssignRecordEntity.OrderAssignRecordId);
            parameters.Add("?OrderId", orderAssignRecordEntity.OrderId);
            parameters.Add("?AdminId", orderAssignRecordEntity.AdminId);
            parameters.Add("?DepartmentId", orderAssignRecordEntity.DepartmentId);
            parameters.Add("?CompanyId", orderAssignRecordEntity.CompanyId);
            parameters.Add("?IsAssign", orderAssignRecordEntity.IsAssign, DbType.Boolean);
            parameters.Add("?Reason", orderAssignRecordEntity.Reason);
            parameters.Add("?UpdateTime", orderAssignRecordEntity.UpdateTime, DbType.DateTime);

            sql = string.Format("{0} {1} WHERE OrderAssignRecordId = ?OrderAssignRecordId;", sql, QUERY);

            return await database.Master.QueryFirstOrDefaultAsync<OrderAssignRecordEntity>(sql, parameters, database.Transaction);

        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除订单指派记录
        /// </summary>
        /// <param name="orderAssignRecordId">订单指派纪录id</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(long orderAssignRecordId)
        {
            string fSql = "DELETE FROM orderassignrecord WHERE OrderAssignRecordId = ?OrderAssignRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderAssignRecordId", orderAssignRecordId, DbType.Int64);
            return await database.Master.ExecuteAsync(fSql, parameters, database.Transaction);
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取订单指派记录
        /// </summary>
        /// <param name="orderAssignRecordId">订单指派纪录id</param>
        /// <returns></returns>
        public async Task<OrderAssignRecordEntity> GetByOrderAssignRecordIdAsync(long orderAssignRecordId)
        {
            string sql = string.Format("{0} WHERE A.OrderAssignRecordId = ?OrderAssignRecordId;", QUERY_JOIN);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderAssignRecordId", orderAssignRecordId, DbType.Int64);
            return await database.Slave.QueryFirstOrDefaultAsync<OrderAssignRecordEntity>(QUERY_JOIN, parameters);
        }
        #endregion

        #region GetByOrdeIdAndAdminId
        /// <summary>
        /// 获取订单指派记录
        /// </summary>
        /// <param name="adminId">管理员Id</param>
        /// <param name="orderId">订单Id</param>
        /// <returns></returns>
        public async Task<OrderAssignRecordEntity> GetByOrdeIdAndAdminIdAsync(long orderId, long adminId, bool isAssign)
        {
            string sql = string.Format("{0} WHERE  OrderId=?OrderId And AdminId=?AdminId AND IsAssign=?IsAssign", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderId, DbType.Int64);
            parameters.Add("?AdminId", adminId, DbType.Int64);
            parameters.Add("?IsAssign", isAssign, DbType.Boolean);
            return await database.Slave.QueryFirstOrDefaultAsync<OrderAssignRecordEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询订单指派记录
        public async Task<(IEnumerable<OrderAssignRecordEntity>, int)> GetListAsync(int? companyId, int? departmentId, long? adminId, long? orderId, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (companyId.HasValue)
            {
                wheres.Add("A.CompanyId=?CompanyId");
                parameters.Add("?CompanyId", companyId.Value, DbType.Int32);
            }
            if (departmentId.HasValue)
            {
                wheres.Add("A.DepartmentId=?DepartmentId");
                parameters.Add("?DepartmentId", departmentId.Value, DbType.Int32);
            }
            if (adminId.HasValue)
            {
                wheres.Add("A.AdminId=?AdminId");
                parameters.Add("?AdminId", adminId.Value, DbType.Int64);
            }
            if (orderId.HasValue)
            {
                wheres.Add("A.OrderId=?OrderId");
                parameters.Add("?OrderId", orderId.Value, DbType.Int64);
            }
            string sql = string.Format(@"{0} {1} ORDER BY OrderAssignRecordId LIMIT {2},{3};
                                    SELECT COUNT(1) 
                                    FROM Orderassignrecord A 
                                    LEFT JOIN `Admin` B ON A.AdminId = B.AdminId 
                                    LEFT JOIN `Orders` C ON C.OrderId = A.OrderId 
                                    LEFT JOIN Department D ON D.DepartmentId = A.DepartmentId {1}",
                        QUERY_JOIN,
                        wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                        (pageIndex - 1) * pageSize, pageSize);

            //获取数据
            return await database.Slave.QueryPageableListAsync<OrderAssignRecordEntity>(sql, parameters);
        }
        #endregion

        public async Task<IEnumerable<OrderAssignRecordEntity>> GetListByOrderIdAsync(long orderId)
        {
            string sql = @"SELECT A.OrderAssignRecordId,A.OrderId,A.AdminId,A.DepartmentId,A.CompanyId,A.IsAssign,A.Reason,A.CreateTime,A.UpdateTime,
                        A.VehicleNo,A.DriverId,A.DriverName,A.IsOrderReceive,A.ReceiveTime,A.AssignEditerId,A.AssignEditer,A.AssignTime,A.AssignTimeOut,
                        B.DepartmentId AS AssignEditerDepartmentId,C.DepartmentName AS AssignEditerDepartmentName,C.CompanyId AS AssignEditerCompanyId,D.CompanyName AS AssignEditerCompanyName  
                        FROM OrderAssignRecord A 
                        LEFT JOIN DepartmentStaff B ON A.AssignEditerId = B.AdminId
                        LEFT JOIN Department C ON B.DepartmentId = C.DepartmentId 
                        LEFT JOIN Company D ON D.CompanyId = C.CompanyId 
                        WHERE A.OrderId = ?OrderId ORDER BY A.CreateTime DESC";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OrderId", orderId, DbType.Int64);
            return await this.database.Slave.QueryAsync<OrderAssignRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 车辆id
        /// </summary>
        /// <param name="driverId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<OrderAssignRecordEntity>> GetListByDriverIdAsync(List<long> driverId)
        {
            string sql = string.Format(@" SELECT
	                                        A.OrderAssignRecordId,
	                                        A.OrderId,
	                                        A.AdminId,
	                                        A.DepartmentId,
	                                        A.CompanyId,
	                                        A.IsAssign,
	                                        A.Reason,
	                                        A.CreateTime,
	                                        A.UpdateTime,
	                                        A.VehicleNo,
	                                        A.DriverId,
	                                        A.DriverName,
	                                        A.IsOrderReceive,
	                                        A.ReceiveTime,
	                                        A.AssignEditerId,
	                                        A.AssignEditer,
	                                        A.AssignTime,
	                                        A.AssignTimeOut,
	                                        B.OrderNo,
                                            B.DriveOrderPromising
                                        FROM
	                                        OrderAssignRecord A
                                        INNER JOIN `orders` B ON A.OrderId = B.OrderId
                                        WHERE
	                                        FIND_IN_SET(A.DriverId ,?DriverIds) ");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverIds", string.Join(",", driverId), DbType.AnsiString);
            return await this.database.Slave.QueryAsync<OrderAssignRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<OrderAssignRecordEntity>> GetOrderAssignListByAdminIds(List<long> adminIds, DateTime? startDate, DateTime? endDate)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (startDate.HasValue)
            {
                wheres.Add("CreateTime >= ?StartDate");
                parameters.Add("?StartDate", startDate.Value.Date, DbType.Date);
            }
            if (endDate.HasValue)
            {
                wheres.Add("CreateTime < ?EndDate");
                parameters.Add("?EndDate", endDate.Value.AddDays(1).Date, DbType.Date);
            }
            string sql = string.Format("{0} WHERE FIND_IN_SET(AssignEditerId,?AssignEditerIds) AND IsOrderReceive = 1 {1} GROUP BY AssignEditerId,OrderId", QUERY,
                wheres.Count > 0 ? " AND " + string.Join(" AND ", wheres) : string.Empty);
            parameters.Add("?AssignEditerIds", string.Join(",", adminIds), DbType.AnsiString);
            return await this.database.Slave.QueryAsync<OrderAssignRecordEntity>(sql, parameters);
        }

        public async Task<OrderAssignRecordEntity> GetLastReceiveRecordByDriverIdAsync(long driverId)
        {
            string sql = string.Format(@"SELECT A.OrderAssignRecordId, A.OrderId, A.AdminId, A.DepartmentId, A.CompanyId, A.IsAssign, A.Reason,
	                                    A.CreateTime, A.UpdateTime, A.VehicleNo, A.DriverId, A.DriverName, A.IsOrderReceive,
	                                    A.ReceiveTime, A.AssignEditerId, A.AssignEditer, A.AssignTime, A.AssignTimeOut, B.OrderNo, B.DriveOrderPromising
                                        FROM OrderAssignRecord A
                                        INNER JOIN `Orders` B ON A.OrderId = B.OrderId 
                                        WHERE A.DriverId = ?DriverId AND A.IsOrderReceive = ?IsOrderReceive AND B.OrderStatus NOT IN ('Closed','Cancel') ORDER BY A.OrderAssignRecordId DESC LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", driverId, DbType.Int64);
            parameters.Add("?IsOrderReceive", true, DbType.Boolean);
            return await this.database.Slave.QueryFirstOrDefaultAsync<OrderAssignRecordEntity>(sql, parameters);
        }

        public async Task<OrderAssignRecordEntity> GetLastAssignRecordByDriverIdAsync(long driverId)
        {
            string sql = string.Format("{0} WHERE DriverId = ?DriverId AND IsAssign = ?IsAssign ORDER BY OrderAssignRecordId  DESC LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DriverId", driverId, DbType.Int64);
            parameters.Add("?IsAssign", true, DbType.Boolean);
            return await this.database.Slave.QueryFirstOrDefaultAsync<OrderAssignRecordEntity>(sql, parameters);
        }

        public async Task<bool> UpdateVehicleNoByDriverIdAsync(long driverId, string vehicleNo)
        {
            return await database.Master.ExecuteAsync($"UPDATE OrderAssignRecord SET VehicleNo = ?VehicleNo WHERE DriverId = ?DriverId", new { driverId, vehicleNo }, database.Transaction)>0;
        }

        public async Task<bool> UpdateOrderReceivedAsync(long orderId)
        {
            string sql = SqlCommands.GetUpdateSql("OrderAssignRecord", new[] { "IsOrderReceive", "ReceiveTime" }, new string[] { "OrderId" }); DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsOrderReceive", true, DbType.Boolean);
            parameters.Add("?ReceiveTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?OrderId", orderId, DbType.Int64);
            return await database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;

        }
    }
}
